package com.dhec.ptms.epr.db;

import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.dhec.ptms.epr.model.EngineerReport;
import com.ionglobal.db.DBI;
import com.ionglobal.lib.SysException;

public class EngineerReportDB {
	private static final Logger logger = LoggerFactory.getLogger(EngineerReportDB.class);
	
	public EngineerReport find(DBI dbi, int id){
		EngineerReport bean = null;
		try {
			String sql = "select * from EPR_EngineerReport where id="+id;
			dbi.execQuery(sql);
			if(dbi.rsNext()){
				bean = new EngineerReport();
				this.handleData(dbi, bean);
			}
			dbi.rsClose();
		} catch (SysException e) {
			logger.error("EngineerReportDB.list(DBI, Map, int, int) Error!", e);
		}
		
		return bean;
	}
	
	public List<EngineerReport> list(DBI dbi, Map<String, String> params, int page, int pagesize){
		ArrayList<EngineerReport> list = new ArrayList<EngineerReport>(0);
		try {
			String sql = "select * from EPR_EngineerReport where 1=1";
			sql += getSqlWhereStr(params, dbi);
			sql+=" order by status asc, id desc";
			dbi.skipToPage(pagesize, page, sql);
			while(dbi.rsNext()){
				EngineerReport bean = new EngineerReport();
				this.handleData(dbi, bean);
				list.add(bean);
			}
			dbi.rsClose();
		} catch (SysException e) {
			logger.error("EngineerReportDB.list(DBI, Map, int, int) Error!", e);
		}
		
		return list;
		
	}
	
	public List<EngineerReport> auditList(DBI dbi, Map<String, String> params, int page, int pagesize){
		ArrayList<EngineerReport> list = new ArrayList<EngineerReport>(0);
		try {
			String sql = "select * from EPR_EngineerReport where 1=1";
			sql += getSqlWhereStr(params, dbi);
			sql+=" order by id asc";
			dbi.skipToPage(pagesize, page, sql);
			while(dbi.rsNext()){
				EngineerReport bean = new EngineerReport();
				this.handleData(dbi, bean);
				list.add(bean);
			}
			dbi.rsClose();
		} catch (SysException e) {
			logger.error("EngineerReportDB.list(DBI, Map, int, int) Error!", e);
		}
		
		return list;
		
	}
	
	public long count(DBI dbi, Map<String, String> params){
		long c = 0;
		try {
			String sql = "select count(*) from EPR_EngineerReport where 1=1"+getSqlWhereStr(params, dbi);
			dbi.execQuery(sql);
			if(dbi.rsNext()){
				c = dbi.rsGetLong(1);
			}
			dbi.rsClose();
		} catch (SysException e) {
			logger.error("EngineerReportDB.list(DBI, params) Error!", e);
		}
		
		return c;
		
	}
	
	public void handleData(DBI dbi, EngineerReport bean) throws SysException{
		bean.setCreatetime(dbi.rsGetDate("createtime"));
		bean.setCreateuser(dbi.rsGetString("createuser"));
		bean.setCode(dbi.rsGetString("code"));
		bean.setId(dbi.rsGetInt("id"));
		bean.setEngineno(dbi.rsGetString("engineno"));
		bean.setRoom(dbi.rsGetString("room"));
		bean.setStandard(dbi.rsGetString("standard"));
		bean.setItem(dbi.rsGetString("item"));
		bean.setManufacturer(dbi.rsGetString("manufacturer"));
		bean.setMaxkw(new BigDecimal(dbi.rsGetDouble("maxkw")).divide(new BigDecimal(1), 2, BigDecimal.ROUND_HALF_UP));
		bean.setMaxnm(new BigDecimal(dbi.rsGetDouble("maxnm")).divide(new BigDecimal(1), 2, BigDecimal.ROUND_HALF_UP));
		bean.setPhase(dbi.rsGetString("phase"));
		bean.setProducedate(dbi.rsGetDate("producedate"));
		bean.setRemark(dbi.rsGetString("remark"));
		bean.setReportno(dbi.rsGetString("reportno"));
		bean.setReporttype(dbi.rsGetString("reporttype"));
		bean.setSampleid(dbi.rsGetString("sampleid"));
		bean.setSenddate(dbi.rsGetDate("senddate"));
		bean.setSenddepart(dbi.rsGetString("senddepart"));
		bean.setSpec(dbi.rsGetString("spec"));
		bean.setStatus(dbi.rsGetInt("status"));
		bean.setSourcedepart(dbi.rsGetString("sourcedepart"));
		bean.setTestcategory(dbi.rsGetString("testcategory"));
		bean.setTestdate(dbi.rsGetDate("testdate"));
		bean.setTrademark(dbi.rsGetString("trademark"));
		bean.setUpdateuser(dbi.rsGetString("updateuser"));
		bean.setUpdtime(dbi.rsGetDate("updtime"));
		bean.setTester(dbi.rsGetString("tester"));
		bean.setRejectreason(dbi.rsGetString("rejectreason"));
		bean.setReview(dbi.rsGetString("review"));
		bean.setAudit(dbi.rsGetString("saudit"));
		bean.setApprove(dbi.rsGetString("approve"));
		bean.setReviewdate(dbi.rsGetDate("reviewdate"));
		bean.setAuditdate(dbi.rsGetDate("auditdate"));
		bean.setApprovedate(dbi.rsGetDate("approvedate"));
	}
	
	public boolean insert(EngineerReport bean, DBI dbi) throws SysException{
		String sql = "INSERT INTO EPR_EngineerReport (id, reportno, spec, reporttype, testcategory,"
				+ " code, engineno, sampleid, phase, room, producedate, testdate, tester, status, "
				+ "item, standard, trademark, senddepart, senddate, sourcedepart, manufacturer, "
				+ "maxkw, maxnm, remark, createuser, createtime) VALUES ("
				+ bean.getId()+", '"+bean.getReportno()+"', '"+dbi.fixSql(bean.getSpec())+"', '"+dbi.fixSql(bean.getReporttype())
				+"', '"+dbi.fixSql(bean.getTestcategory())+"', '"+dbi.fixSql(bean.getCode())+"', '"+dbi.fixSql(bean.getEngineno())+"', '"
				+ dbi.fixSql(bean.getSampleid())+"', '"+dbi.fixSql(bean.getPhase())+"', '"+dbi.fixSql(bean.getRoom())
				+"', "+dbi.getInsertDate(bean.getProducedate())+", "+dbi.getInsertDate(bean.getTestdate())+", '"
				+dbi.fixSql(bean.getTester())+"', "+bean.getStatus()+", '"+dbi.fixSql(bean.getItem())+"', '"
				+dbi.fixSql(bean.getStandard())+"', '"+dbi.fixSql(bean.getTrademark())+"', '"+dbi.fixSql(bean.getSenddepart())+"', "
				+dbi.getInsertDate(bean.getSenddate())+", '"+dbi.fixSql(bean.getSourcedepart())+"', '"
				+dbi.fixSql(bean.getManufacturer())+"', "+bean.getMaxkw()+", "+bean.getMaxnm()+", '"
				+dbi.fixSql(bean.getRemark())+"', '"+dbi.fixSql(bean.getCreateuser())+"', "+dbi.getCurrentDateTime()+")";
		
		sql = trunc(sql);
		
		return dbi.execute(sql);
	}
	
	public boolean update(EngineerReport bean, DBI dbi) throws SysException{
		String sql = "update EPR_EngineerReport set spec='"+dbi.fixSql(bean.getSpec())
				+"', reporttype='"+dbi.fixSql(bean.getReporttype())+"', testcategory='"
				+dbi.fixSql(bean.getTestcategory())+"', code='"+dbi.fixSql(bean.getCode())
				+"', engineno='"+dbi.fixSql(bean.getEngineno())+"', sampleid='"
				+ dbi.fixSql(bean.getSampleid())+"', phase='"+dbi.fixSql(bean.getPhase())
				+"', room='"+dbi.fixSql(bean.getRoom())+"', producedate="
				+dbi.getInsertDate(bean.getProducedate())+", testdate="
				+dbi.getInsertDate(bean.getTestdate())+", tester='"+dbi.fixSql(bean.getTester())
				+"', status="+bean.getStatus()+", item='"+dbi.fixSql(bean.getItem())
				+"', standard='"+dbi.fixSql(bean.getStandard())+"', trademark='"
				+dbi.fixSql(bean.getTrademark())+"', senddepart='"
				+dbi.fixSql(bean.getSenddepart())+"', senddate="
				+dbi.getInsertDate(bean.getSenddate())+", sourcedepart='"
				+dbi.fixSql(bean.getSourcedepart())+"', manufacturer='"
				+dbi.fixSql(bean.getManufacturer())+"', "
				+ "maxkw="+bean.getMaxkw()+", maxnm="+bean.getMaxnm()+", remark='"
				+dbi.fixSql(bean.getRemark())+"', updateuser='"+dbi.fixSql(bean.getUpdateuser())
				+"', updtime="+dbi.getCurrentDateTime()+",reportno='"+dbi.fixSql(bean.getReportno())+"'"
				+(bean.getRejectreason()!=null?", rejectreason='"+dbi.fixSql(bean.getRejectreason())+"'":"")
				+(bean.getReview()!=null?", review='"+dbi.fixSql(bean.getReview())+"'":"")
				+(bean.getAudit()!=null?", saudit='"+dbi.fixSql(bean.getAudit())+"'":"")
				+(bean.getApprove()!=null?", approve='"+dbi.fixSql(bean.getApprove())+"'":"")
				+(bean.getReviewdate()!=null?", reviewdate="+dbi.getInsertDate(bean.getReviewdate()):"")
				+(bean.getAuditdate()!=null?", auditdate="+dbi.getInsertDate(bean.getAuditdate()):"")
				+(bean.getApprovedate()!=null?", approvedate="+dbi.getInsertDate(bean.getApprovedate()):"")
				+" where id="+bean.getId();
		
		sql = trunc(sql);
		
		return dbi.execute(sql);
	}
	
	public boolean delete(DBI dbi, int reportid) throws SysException{
		return dbi.execute("delete from EPR_EngineerReport where id="+reportid);
	}
	
	private String getSqlWhereStr(Map<String,String> params, DBI dbi){
		String sql="";
		if(params.containsKey("reportno")){
			sql+=" and reportno like '%"+dbi.fixSql(params.get("reportno"))+"%'";
		}
		if(params.containsKey("spec")){
			sql+=" and spec like '%"+dbi.fixSql(params.get("spec"))+"%'";
		}
		if(params.containsKey("code")){
			sql+=" and code like '"+dbi.fixSql(params.get("code"))+"'";
		}
		if(params.containsKey("testcategory")){
			sql+=" and testcategory='"+dbi.fixSql(params.get("testcategory"))+"'";
		}
		if(params.containsKey("engineno")){
			sql+=" and engineno like '%"+dbi.fixSql(params.get("engineno"))+"%'";
		}
		if(params.containsKey("producedate")){
			sql+=" and producedate="+dbi.getInsertDate(params.get("producedate"));
		}
		if(params.containsKey("testdate")){
			sql+=" and testdate="+dbi.getInsertDate(params.get("testdate"));
		}
		if(params.containsKey("tester")){
			sql+=" and tester like '%"+dbi.fixSql(params.get("tester"))+"%'";
		}
		if(params.containsKey("room")){
			sql+=" and room like '%"+dbi.fixSql(params.get("room"))+"%'";
		}
		if(params.containsKey("createuser")){
			sql+=" and createuser='"+dbi.fixSql(params.get("createuser"))+"'";
		}
		if(params.containsKey("status")){
			String status = params.get("status");
			if(status.indexOf(",")!=-1){
				String[] statusArr = status.split(",");
				sql+=" and status in (";
				for(int i=0;i<statusArr.length;i++){
					if(i>0)
						sql+=",";
					sql+=statusArr[i];
				}
				sql+=")";
			}else
				sql+=" and status="+status;
		}
		
		return sql;
	}
	
	public boolean exist(DBI dbi, String reportno){
		long c = 0;
		try {
			String sql = "select count(*) from EPR_EngineerReport where reportno='"+reportno+"'";
			dbi.execQuery(sql);
			if(dbi.rsNext()){
				c = dbi.rsGetInt(1);
			}
			dbi.rsClose();
		} catch (SysException e) {
			logger.error("EngineerReportDB.list(DBI, params) Error!", e);
		}
		
		return c>0;
		
	}
	
	private String trunc(String str){
		/*String tmp;
		try {
			if(str!=null)
				tmp = new String(str.getBytes("GBK"), "ISO-8859-1");
			else
				tmp = str;
		} catch (UnsupportedEncodingException e) {
			return str;
		}*/
		return str;
	}
}
